package com.ai.smlcore.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ai.frame.logger.Logger;
import com.ai.frame.logger.LoggerFactory;

/**
 * POI 解析 2003-2007 版本 Excel 文件
 *
 */
public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getUtilLog(ExcelUtil.class);

    /**
     * 读取 office 2003 excel
     *
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static int reading2003Row = 0;
    public static int reading2003total = 1;

    private static List<Map<String, String>> read2003Excel(InputStream inputStream, String fileName)
            throws IOException {
        List<Map<String, String>> list = new LinkedList<Map<String, String>>();
        HSSFWorkbook hwb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        reading2003total = sheet.getPhysicalNumberOfRows();
        logger.info("", "读取 office 2003 excel 内容如下：");
        for (int i = 5; i <= sheet.getPhysicalNumberOfRows(); i++) {
            logger.info("", "解析excel" + i);
            row = sheet.getRow(i);
            if (row != null) {
                reading2003Row = i + 1;
                Map<String, String> linked = new HashMap<String, String>();
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum() - 1; j++) {
                    try {
                        cell = row.getCell(j);
                        if (cell != null && !cell.toString().trim().equals("")) {
                            DecimalFormat df = new DecimalFormat("0");// 格式化
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = df.format(cell.getNumericCellValue());
                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = nf.format(cell.getNumericCellValue());
                                } else {
                                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            default:
                                value = cell.toString();
                            }
                            if (j == 1) {
                                if (!isMobileNumber(value.toString())) {
                                    linked.clear();
                                    linked.put("flag", "flase");
                                    linked.put("FILE_NM", fileName);
                                    linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列手机格式错误");
                                    linked.put("ERROR_DESC", "手机格式错误");
                                    break;
                                }
                            }
                            // 判断是否存在手机号
                            boolean str = true;// 判断是否含有重复数值
                            if (str) {// 当前插入数值正确
                                if (j == 0) {
                                    linked.put("LIN_CUST_NM", value.toString());
                                } else if (j == 1) {
                                    linked.put("RESV_MOBNUM", value.toString());
                                } else if (j == 2) {
                                    linked.put("RSVT_MCDS_UNIT_ID", value.toString());
                                } else if (j == 3) {
                                    linked.put("RSVT_DATE", value.toString());
                                }
                            } else {
                                break;
                            }

                        } else {
                            linked.clear();
                            linked.put("flag", "flase");
                            linked.put("FILE_NM", fileName);
                            linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列不能为空");
                            linked.put("ERROR_DESC", "有字段为空");
                            break;
                        }
                    } catch (Exception e) {
                        linked.clear();
                        linked.put("flag", "flase");
                        linked.put("FILE_NM", fileName);
                        linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "解析异常");
                        linked.put("ERROR_DESC", "解析异常");
                        break;
                    }
                }
                linked.put("flag", "true");
                list.add(linked);
            }
        }
        inputStream.close();
        return list;

    }

    /**
     * 读取 Office 2007 excel
     */
    private List<Map<String, String>> read2007Excel(InputStream inputStream, String fileName) throws IOException {
        List<Map<String, String>> list = new LinkedList<Map<String, String>>();
        XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        logger.info("", "读取 office 2007 excel 内容如下：");
        for (int i = 5; i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String, String> linked = new HashMap<String, String>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum() - 1; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {
                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    continue;
                }
                if (j == 1) {
                    linked.put("CUST_NM", value.toString());
                } else if (j == 2) {
                    linked.put("PHONE_NUM", value.toString());
                } else if (j == 3) {
                    linked.put("PHONE_NUM", value.toString());
                } else if (j == 4) {
                    linked.put("PHONE_NUM", value.toString());
                }
            }
            list.add(linked);
        }
        return list;
    }

    /**
     * 获取2003Excel第一个sheet页中的行数
     *
     * @param file
     * @return
     * @throws Exception
     */
    private static int getExcel2003RowNum(File file) throws Exception {
        HSSFWorkbook workBook = new HSSFWorkbook(FileUtils.openInputStream(file));
        HSSFSheet sheet = workBook.getSheetAt(0); // 拿到第一个sheet页
        int rows = sheet.getLastRowNum() + 1;// 几行
        workBook.close();
        return rows;
    }

    /**
     * 获取2007Excel第一个sheet页中的行数
     *
     * @param file
     * @return
     * @throws Exception
     */
    private static int getExcel2007RowNum(File file) throws Exception {
        XSSFWorkbook workBook = new XSSFWorkbook(FileUtils.openInputStream(file));
        XSSFSheet sheet = workBook.getSheetAt(0);
        int rows = sheet.getLastRowNum() + 1;
        workBook.close();
        return rows;
    }

    /**
     * 获得一个2003Excel的第一个sheet页中的有效行数
     *
     * @param file
     * @param beginRow
     * @return
     * @throws Exception
     */
    private static int getExcel2003ValidRowNum(File file, int beginRow) throws Exception {
        HSSFWorkbook workBook = new HSSFWorkbook(FileUtils.openInputStream(file));
        HSSFSheet sheet = workBook.getSheetAt(0); // 拿到第一个sheet页
        int rows = sheet.getLastRowNum() + 1;// 几行
        HSSFRow tmp = null;
        int i = rows;
        while (true) {
            tmp = sheet.getRow(--i);
            if (tmp != null && tmp.getCell(4) == null
                    || tmp != null && tmp.getCell(4) != null && tmp.getCell(4).getCellType() == Cell.CELL_TYPE_BLANK) {
                rows--;
            }
            if (i == beginRow - 1) {
                break;
            }
        }
        workBook.close();
        return rows - beginRow;
    }

    /**
     * 获得一个2007Excel的第一个sheet页中的有效行数
     *
     * @param file
     * @param beginRow
     * @return
     * @throws Exception
     */
    private static int getExcel2007ValidRowNum(File file, int beginRow) throws Exception {
        XSSFWorkbook workBook = new XSSFWorkbook(FileUtils.openInputStream(file));
        XSSFSheet sheet = workBook.getSheetAt(0); // 拿到第一个sheet页
        int rows = sheet.getLastRowNum() + 1;// 几行
        XSSFRow tmp = null;
        int i = rows;
        while (true) {
            tmp = sheet.getRow(--i);
            if (tmp != null && tmp.getCell(4) == null
                    || tmp != null && tmp.getCell(4) != null && tmp.getCell(4).getCellType() == Cell.CELL_TYPE_BLANK) {
                rows--;
            }
            if (i == beginRow - 1) {
                break;
            }
        }
        workBook.close();
        return rows - beginRow;
    }

    /**
     * 对外提供读取 Excel的方法
     */
    public List<Map<String, String>> readExcel(InputStream inputStream, String fileName) throws IOException {
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(inputStream, fileName);
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(inputStream, fileName);
        } else {
            throw new IOException("不支持的文件类型!");
        }
    }

    /**
     * 对外提供读取 Excel有效行数的方法
     */
    public static int getExcelValidRowNum(File file, int beginRow) throws Exception {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return getExcel2003ValidRowNum(file, beginRow);
        } else if ("xlsx".equals(extension)) {
            return getExcel2007ValidRowNum(file, beginRow);
        } else {
            throw new Exception("解析Excel行数出错!");
        }
    }

    /**
     * 对外提供解析Excel行数的方法
     *
     * @param file
     * @param filePath
     * @return
     * @throws Exception
     */
    public static int getExcelRowNum(File file) throws Exception {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return getExcel2003RowNum(file);
        } else if ("xlsx".equals(extension)) {
            return getExcel2007RowNum(file);
        } else {
            throw new Exception("文件类型不支持!");
        }
    }

    public static void main(String[] args) throws Exception {
        ExcelUtil u = new ExcelUtil();
        File file = new File("D:\\FTP\\home\\ftpuser\\market\\cmpgn_upload\\MARKET_ORDER_CUST_TRG.xls");
        FileInputStream fs = new FileInputStream(file);
        System.out.println(u.readExcel(fs, "market.xls"));
    }

    public static File createTempFile(InputStream is, String extName) throws Exception {
        // 创建临时文件，用于解压
        File file = File.createTempFile("TempFile_", "." + extName);
        FileOutputStream os = new FileOutputStream(file);
        try {
            byte[] bytes = new byte[2048];
            int read;
            if (is != null) {
	            while ((read = is.read(bytes)) != -1) {
	                os.write(bytes, 0, read);
	            }
            }
        } finally {
            if (is != null) {
                is.close();
            }
            os.close();
        }
        return file;
    }

    public static List<List<Object>> read2003ExcelForO2O(InputStream inputStream, String fileName) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook hwb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        reading2003total = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                reading2003Row = i + 1;
                List<Object> linked = new LinkedList<Object>();
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum() - 1; j++) {

                    try {
                        cell = row.getCell(j);
                        if (cell != null && !cell.toString().trim().equals("")) {
                            DecimalFormat df = new DecimalFormat("0");// 格式化
                            // number
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = df.format(cell.getNumericCellValue());
                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = nf.format(cell.getNumericCellValue());
                                } else {
                                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            default:
                                value = cell.toString();
                            }
                            linked.add(value);
                        } else {
                            linked.clear();
                            linked.add("error");
                            linked.add(fileName + "");
                            linked.add(i + 1 + "行" + (j + 1) + "列不能为空");
                            linked.add("含有空字段");
                            break;
                        }
                    } catch (Exception e) {
                        linked.clear();
                        linked.add("error");
                        linked.add(fileName);
                        linked.add(i + 1 + "行" + (j + 1) + "列解析异常");
                        linked.add("解析异常");
                        break;
                    }
                }
                list.add(linked);
            }
        }
        inputStream.close();
        return list;

    }

    /**
     * 对产品添加供解析Excel方法，（此模板不支持公共调用）
     *
     * @param inputStream
     * @param fileName
     * @return list 产品参数
     * @throws Exception
     */
    public List<Object> read2003ExcelForProdData(InputStream inputStream, String fileName) throws IOException {
        HSSFWorkbook hwb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        List<Object> linked = new LinkedList<Object>();
        for (int i = 0; i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row != null) {

                try {
                    cell = row.getCell(1);

                    boolean isMerge = isMergedRow(sheet, i, cell.getColumnIndex());
                    // 判断是否具有合并单元格(行)
                    if (isMerge) {
                        continue;
                    }

                    if (!cell.toString().trim().equals("")) {
                        DecimalFormat df = new DecimalFormat("0");// 格式化
                        // number
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                value = df.format(cell.getNumericCellValue());
                            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                value = nf.format(cell.getNumericCellValue());
                            } else {
                                value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        default:
                            value = cell.toString();
                        }
                        linked.add(value);
                    } else {
                        linked.add("");
                    }
                } catch (Exception e) {
                    linked.clear();
                    linked.add("error");
                    linked.add(fileName);
                    linked.add(i + 1 + "行" + 2 + "列解析异常");
                    linked.add("解析异常");
                    break;
                }
            }
        }
        inputStream.close();
        return linked;

    }

    /**
     * 判断合并了行
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    public boolean match(String a) {
        String regExp = "^[1]([3][0-9]{1}|59|58|88|89)[0-9]{8}$";

        Pattern p = Pattern.compile(regExp);

        Matcher m = p.matcher(a);

        return m.find();// boolean

    }

    public static boolean isMobileNumber(String mobiles) {
        return Pattern.compile("^((13[0-9])|(15[^4,\\D])|(18[^1^4,\\D]))\\d{8}").matcher(mobiles).matches();
    }

    public static boolean isCustMobileNumber(String mobiles) {
        return Pattern.compile("^1\\d{10}").matcher(mobiles).matches();
    }

    /**
     * 对外提供读取 Excel的方法
     *
     * @throws Exception
     */
    public List<Map<String, String>> readExcelForMarket(InputStream inputStream, String fileName, String flag)
            throws Exception {
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003ExcelForCust(inputStream, fileName, flag);
            // return read2003ExcelJXL(inputStream, 6);
        } else if ("xlsx".equals(extension)) {
            return read2007ExcelForCust(inputStream, fileName, flag);
        } else {
            throw new IOException("不支持的文件类型!");
        }
    }

    private List<Map<String, String>> read2003ExcelForCust(InputStream inputStream, String fileName, String flag)
            throws IOException {
        List<Map<String, String>> list = new LinkedList<Map<String, String>>();
        HSSFWorkbook hwb = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;

        int totalM = 0;
        List<String> tableHead = new ArrayList<String>();// 存放内容不确定的表头信息
        int length = 0;
        // 表格文件的行号从0开始
        for (int i = 5; i <= sheet.getPhysicalNumberOfRows(); i++) {
            logger.info("", "解析excel" + i);
            row = sheet.getRow(i);
            if (i == 5) {
                length = row.getLastCellNum();
            }
            if (row != null) {
                Map<String, String> linked = new HashMap<String, String>();
                // int total = 0;
                if ("market".equals(flag)) {
                    if (i != 5 && length != totalM) {// row.getLastCellNum()该方法如何做-1操作；不可以对row的最后一行操作
                        linked.clear();
                        linked.put("flag", "false");
                        linked.put("row", i + 1 + "");
                        linked.put("FILE_NM", fileName);
                        linked.put("ERROR_RSN_DESC", i + 1 + "模版错误");
                        linked.put("ERROR_DESC", "目标客户模版不正确");
                        list.add(linked);
                        break;
                    } else if (i == 5) {// 获取表头列数
                        totalM = row.getLastCellNum();
                    }

                }
                StringBuilder custExtdData = new StringBuilder();// 用于保存所有的附属信息，（key1：value1；key2：value2...）
                for (int j = 0; j <= length - 1; j++) {
                    try {
                        cell = row.getCell(j);// 获取每一个单元格对象
                        if (cell != null && !"".equals(cell.toString().trim()) || "market".equals(flag)) {// 营销目标客户导入时，空单元格也看作符号要求
                            DecimalFormat df = new DecimalFormat("0");// 格式化
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            DecimalFormat nf = new DecimalFormat("0");// 格式化数字

                            int type = 3;
                            if (cell != null) {
                                type = cell.getCellType();
                            }

                            switch (type) {
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = df.format(cell.getNumericCellValue());
                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = nf.format(cell.getNumericCellValue());
                                } else {
                                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            default:
                                value = cell.toString();
                            }

                            if ("market".equals(flag)) {
                                if (i != 5) {
                                    String valueV = "".equals(value.toString()) || "*".equals(value.toString()) ? "null"
                                        : value.toString();// 如果该字段是空，那么转存null
                                    if (j >= 1 && j <= totalM - 2) {
                                        custExtdData.append(tableHead.get(j) + ":" + valueV + ",");
                                    } else if (j == totalM - 1) {
                                        // valueV =
                                        // ("".equals(value.toString())?"null":value.toString());//如果该字段是空，那么转存null
                                        custExtdData.append(tableHead.get(j) + ":" + valueV);
                                        linked.put("CUST_EXTD_DATA", custExtdData.toString());
                                    } else if (j == 0) {
                                        if (!isCustMobileNumber(value.toString())) {
                                            linked.clear();
                                            linked.put("flag", "false");
                                            linked.put("row", i + 1 + "");
                                            linked.put("FILE_NM", fileName);
                                            linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列手机格式错误");
                                            linked.put("ERROR_DESC", "手机格式错误");
                                            break;
                                        }
                                        linked.put("RESV_MOBNUM", value.toString());
                                    }
                                    linked.put("LIN_CUST_NM", "");
                                } else {// 第6行为表头
                                    tableHead.add(j, (String)value);
                                }

                            } else {

                                if (j == 1) {
                                    if (!isCustMobileNumber(value.toString())) {
                                        linked.clear();
                                        linked.put("flag", "false");
                                        linked.put("row", i + 1 + "");
                                        linked.put("FILE_NM", fileName);
                                        linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列手机格式错误");
                                        linked.put("ERROR_DESC", "手机格式错误");
                                        break;
                                    }
                                }
                                if (j == 0) {// 插入客户姓名
                                    linked.put("LIN_CUST_NM", value.toString());
                                } else if (j == 1) {// 客户电话
                                    linked.put("RESV_MOBNUM", value.toString());
                                } else if (j == 2) {// 商品ID
                                    linked.put("RSVT_MCDS_UNIT_ID", value.toString());
                                } else if (j == 3) {// 日期
                                    linked.put("RSVT_DATE", value.toString());
                                }

                            }

                            // 判断如果营销活动客户没有值默认填写未知
                        } else {
                            linked.clear();
                            linked.put("flag", "false");
                            linked.put("FILE_NM", fileName);
                            linked.put("row", i + 1 + "");
                            linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列不能为空");
                            linked.put("ERROR_DESC", "有字段为空");
                            break;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        linked.clear();
                        linked.put("flag", "false");
                        linked.put("row", i + 1 + "");
                        linked.put("FILE_NM", fileName);
                        linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "解析异常");
                        linked.put("ERROR_DESC", "解析异常");

                        break;
                    }
                }
                if (i != 5 || "order".equals(flag)) {// market的第6行时不执行
                    list.add(linked);
                }
            }
        }
        inputStream.close();
        return list;

    }

    /**
     * 读取 Office 2007 excel
     */
    private static List<Map<String, String>> read2007ExcelForCust(InputStream inputStream, String fileName, String flag)
            throws IOException {
        List<Map<String, String>> list = new LinkedList<Map<String, String>>();
        XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;

        int totalM = 0;
        List<String> tableHead = new ArrayList<String>();// 存放内容不确定的表头信息
        int length = 0;
        logger.info("", "读取 office 2003 excel 内容如下：");
        for (int i = 5; i <= sheet.getPhysicalNumberOfRows(); i++) {
            logger.info("", "解析07版excel" + "第" + i + "行");
            row = sheet.getRow(i);
            if (i == 5) {
                length = row.getLastCellNum();
            }
            if (row != null) {
                Map<String, String> linked = new HashMap<String, String>();
                // int total = 0;
                if ("market".equals(flag)) {
                    if (i != 5 && length != totalM) {
                        linked.clear();
                        linked.put("flag", "false");
                        linked.put("row", i + 1 + "");
                        linked.put("FILE_NM", fileName);
                        linked.put("ERROR_RSN_DESC", i + 1 + "模版错误");
                        linked.put("ERROR_DESC", "目标客户模版不正确");
                        list.add(linked);
                        break;
                    } else if (i == 5) {// 获取表头列数
                        totalM = row.getLastCellNum();
                    }
                }
                StringBuilder custExtdData = new StringBuilder();// 用于保存所有的附属信息，（key1：value1；key2：value2...）
                for (int j = 0; j <= length - 1; j++) {
                    try {
                        cell = row.getCell(j);
                        if (cell != null && !"".equals(cell.toString().trim()) || "market".equals(flag)) {// 如果营销导入目标客户，那么单元格为空也看作符合要求
                            DecimalFormat df = new DecimalFormat("0");// 格式化
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            DecimalFormat nf = new DecimalFormat("0");// 格式化数字

                            int type = 3;
                            if (cell != null) {
                                type = cell.getCellType();
                            }

                            switch (type) {
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = df.format(cell.getNumericCellValue());
                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                    value = nf.format(cell.getNumericCellValue());
                                } else {
                                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            default:
                                value = cell.toString();
                            }

                            if ("market".equals(flag)) {
                                if (i != 5) {
                                    String valueV = "".equals(value.toString()) || "*".equals(value.toString())
                                            || value == null ? "null" : value.toString();// 如果该字段是空，那么转存null
                                    if (j >= 1 && j <= totalM - 2) {
                                        custExtdData.append(tableHead.get(j) + ":" + valueV + ",");
                                    } else if (j == totalM - 1) {
                                        custExtdData.append(tableHead.get(j) + ":" + valueV);
                                        linked.put("CUST_EXTD_DATA", custExtdData.toString());
                                    } else if (j == 0) {
                                        if (!isCustMobileNumber(value.toString())) {
                                            linked.clear();
                                            linked.put("flag", "false");
                                            linked.put("row", i + 1 + "");
                                            linked.put("FILE_NM", fileName);
                                            linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列手机格式错误");
                                            linked.put("ERROR_DESC", "手机格式错误");
                                            break;
                                        }
                                        linked.put("RESV_MOBNUM", value.toString());
                                    }
                                    linked.put("LIN_CUST_NM", "");
                                } else {// 第6行为表头
                                    tableHead.add(j, (String)value);
                                }

                            } else {
                                if (j == 1) {
                                    if (!isCustMobileNumber(value.toString())) {
                                        linked.clear();
                                        linked.put("flag", "false");
                                        linked.put("row", i + 1 + "");
                                        linked.put("FILE_NM", fileName);
                                        linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列手机格式错误");
                                        linked.put("ERROR_DESC", "手机格式错误");
                                        break;
                                    }
                                }
                                if (j == 0) {// 插入客户姓名
                                    linked.put("LIN_CUST_NM", value.toString());
                                } else if (j == 1) {// 客户电话
                                    linked.put("RESV_MOBNUM", value.toString());
                                } else if (j == 2) {// 商品ID
                                    linked.put("RSVT_MCDS_UNIT_ID", value.toString());
                                } else if (j == 3) {// 日期
                                    linked.put("RSVT_DATE", value.toString());
                                }

                            }
                        } else {
                            linked.clear();
                            linked.put("flag", "false");
                            linked.put("FILE_NM", fileName);
                            linked.put("row", i + 1 + "");
                            linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "列不能为空");
                            linked.put("ERROR_DESC", "有字段为空");
                            break;
                        }
                    } catch (Exception e) {
                        linked.clear();
                        linked.put("flag", "false");
                        linked.put("row", i + 1 + "");
                        linked.put("FILE_NM", fileName);
                        linked.put("ERROR_RSN_DESC", i + 1 + "行" + (j + 1) + "解析异常");
                        linked.put("ERROR_DESC", "解析异常");
                        break;
                    }
                }
                if (i != 5 || "order".equals(flag)) {// market的第6行时不执行
                    list.add(linked);
                }
            }
        }
        inputStream.close();
        return list;
    }
}
